<?php
define('MYSQL_DATE_FORMAT','Y-m-d');
define('MYSQL_TIME_FORMAT','H:i:s');
define('MYSQL_DATETIME_FORMAT','Y-m-d H:i:s');

class MysqlRow extends SmartArray {
    private $is_new;
    private $table;
    
    const PHP = 0;
    const MYSQL = 1;
    const RAW = 2;
    
    public function __construct($table,$array,$source = self::PHP) {
        assert('$table instanceof MysqlTable;');

        if ( !isset($array[$table->primary_key_name]) ) {
            $array[$table->primary_key_name] = null;
        }
        $this->is_new = ($array[$table->primary_key_name] === null);
        $this->table = $table;
        $translate_to = $table->translate_to;
        if ($source == self::PHP) {
            foreach ($array as $field=>&$val) {
                if (isset($translate_to[$field])) $val = call_user_func($translate_to[$field],$val);
            }
        }
            
        parent::__construct($array);
    }
    
    public function set($field,$val,$source = self::PHP) {
        /* Not sure we need this...
        switch($source) {
            case default:
                $val = call_user_func(static::$translate_to_db[$field],$val);
                break;
        }
        */
        if (isset($this->table->translate_to[$field]))
            $val = call_user_func($this->table->translate_to[$field],$val);
        if ($field === $this->table->primary_key_name)  throw new Exception("Setting $field is not permitted!");
        else parent::set($field,$val);
    }
    
    public function set_array($array,$source = self::PHP) {
        foreach ($array as $field=>$val) {
            $this->set($field,$val,$source);
        }
        return $this->is_modified();
    }
    
    private function set_key($val) {
        parent::set($this->table->primary_key_name,$val);
    }

    public function get_key() {
        return $this->get($this->table->primary_key_name);
    }
    
    public function is_new() {
        return $this->is_new;
    }
    
    public function commit() {
        parent::commit();
        $this->is_new = false;
    }
    
    public function save() {
        $table = $this->table;
        if ($this->is_new()) {
            $table->execute_query($table->get_insert_query(array($this)));
            $this->set_key($table->db_link->insert_id);
        }
        elseif ($this->is_modified()) $table->execute_query($table->get_update_query($this));
        $this->commit();
    }
    
    public function mysql_get($field) {
        $table = $this->table;
        $val = $this->get($field);
        if ($val === null) {
            return 'NULL';
        } else {
            $quote = $table->field_info[$field]->quote;
            return $quote.$table->db_link->escape_string($val).$quote;
        }
    }

    public function mysql_insert_values($fields) {
        $vals = array();
        foreach ($fields as $field) {
            $vals[] = $this->mysql_get($field);
        }
        return '('.implode(',',$vals).')';
    }
    
    public function mysql_update_values() {
        $update_pairs = array();
        foreach ($this->modified as $field=>$val) {
            $update_pairs[] = $field.'='.$this->mysql_get($field);
        }
        return implode(',',$update_pairs);
    }
}

/* - bit flags for flags field returned by fetch_field()
       NOT_NULL_FLAG = 1
       PRI_KEY_FLAG = 2
       UNIQUE_KEY_FLAG = 4
       BLOB_FLAG = 16
       UNSIGNED_FLAG = 32
       ZEROFILL_FLAG = 64
       BINARY_FLAG = 128
       ENUM_FLAG = 256
       AUTO_INCREMENT_FLAG = 512
       TIMESTAMP_FLAG = 1024
       SET_FLAG = 2048
       NUM_FLAG = 32768
       PART_KEY_FLAG = 16384
       GROUP_FLAG = 32768
       UNIQUE_FLAG = 65536
*/

define('NOT_NULL_FLAG',1);
define('PRI_KEY_FLAG',2);
define('AUTO_INCREMENT_FLAG',512);

// This is the factory class
class MysqlTable {
    public $name;
    public $primary_key_name;
    public $db_link;
    public $field_info = array();
    public $is_required = array();
    public $default_insert_array = array();
    public $translate_to = array();
    
    public function __construct ($db,$table) {
        $query = "SELECT * FROM $table LIMIT 1";
        if ( $result = $db->query($query) ) {
            while ($finfo = $result->fetch_field()) {
                switch ($finfo->type) {
                    case 10:
                    case 252:
                    case 253:
                    case 254:
                        $finfo->quote = "'";
                        break;
                    default:
                        $finfo->quote = '';
                        break;
                }

                $field = $finfo->name;
                $flags = $finfo->flags;
                $this->field_info[$field] = $finfo;
                $this->is_required[$field] = ($flags & NOT_NULL_FLAG) && !($flags & AUTO_INCREMENT_FLAG) 
                                                && ($finfo->def === '');
                if ($flags & PRI_KEY_FLAG) $this->primary_key_name = $field;
            }
        } else throw new Exception("Unable to load field info from $table: ");

        $this->db_link = $db;
        $this->name = $table;
        $this->set_default_insert_array();
    }
    
    public function set_translate($field,$fcn) {
        $this->translate_to[$field] = $fcn;
    }

    public function fetch_rows($where = 1,$joins = '') {
        $rows = array();
        $result = $this->execute_query("SELECT * FROM {$this->name} $joins WHERE $where");
        while ( $assoc = $result->fetch_assoc() ) {
            $rows[] = new MysqlRow($this,$assoc,MysqlRow::MYSQL);
        }
        return $rows;
    }

    public function fetch_row_by_id($id) {
        $rows = $this->fetch_rows($this->primary_key_name.'='.(int) $id);
        return count($rows) ? $rows[0] : false;
    }
    
    public function fetch_row_by_field($field,$val) {
        $db = $this->db_link;
        $quote = $this->field_info[$field]->quote;
        $field = $db->escape_string($field);
        $val = $db->escape_string($val);
        $rows = $this->fetch_rows("$field=$quote$val$quote");
        return count($rows) ? $rows[0] : false;
    }

    public function new_row($array) {
        return new MysqlRow($this,$array);
    }

    public function set_default_insert_array ($array = null) {
        if ($array === null) {
            $this->default_insert_array = array_diff(array_keys($this->is_required),array($this->primary_key_name));
        } else {
            foreach ($this->is_required as $field=>$required) {
                if ($required && !isset($array[$field])) throw new Exception("$field is required for INSERT!");
            }
            $this->default_insert_array = $array;
        }
    }
    
    public function get_insert_query($rows) {
        $values = array();
        foreach ($rows as $row) {
            $values[] = $row->mysql_insert_values($this->default_insert_array);
        }
        return 'INSERT INTO '.$this->name.' ('.implode(',',$this->default_insert_array).') VALUES '.
                implode(',',$values);
    }
    
    public function get_update_query($row) {
        return "UPDATE {$this->name} SET ".$row->mysql_update_values().
                " WHERE {$this->primary_key_name}=".$row->mysql_get($this->primary_key_name);
    }

    public function execute_query($query) {
        //var_dump($query);
        $db = $this->db_link;
        $result = $db->query($query);
        if (!$result) throw new Exception("Error {$db->errno}: {$db->error} $query");
        return $result;
    }
    
    public function delete_row_by_id($id) {
        $query = 'DELETE FROM '.$this->name.' WHERE '.$this->primary_key_name.'='.(int) $id;
        return $this->execute_query($query);
    }
    
    public function save_rows($rows) {
        $inserts = array();
        $updates = array();
        foreach ($rows as $row) {
            if ($row->is_new()) $inserts[] = $row;
            elseif ($row->is_modified()) $updates[] = $row;
        }
        $num_inserts = count($inserts);
        $num_updates = count($updates);
        $num_queries = ($num_inserts ? 1 : 0 ) + $num_updates;
        if ($num_queries) {
            $this->execute_query('START TRANSACTION');
            if ($num_inserts) $this->execute_query($this->get_insert_query($inserts));
            if ($num_updates) {
                foreach ($updates as $row) {
                    $this->execute_query($this->get_update_query($row));
                }
            }
            $this->execute_query('COMMIT');
            foreach($rows as $row) $row->commit();
        }
    }
}

?>